﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using StockTrader.DAL.IDAL;
using StockTrader.DAL.MSSQLHelper;
using StockTrader.BusinessService.DataContract;

namespace StockTrader.DAL.SQLserver
{
    public class DocType : IDocType
    {
/*
                private SqlConnection _internalConnection;
                private SqlTransaction _internalADOTransaction = null;
        
                public void BeginADOTransaction()
                {
                    if (_internalConnection.State != System.Data.ConnectionState.Open)
                        _internalConnection.Open();
                    _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
                }
                public void RollBackTransaction()
                {
                    _internalADOTransaction.Rollback();
                    _internalADOTransaction = null;
                }
                public void CommitADOTransaction()
                {
                    _internalADOTransaction.Commit();
                    _internalADOTransaction = null;
                }
                public void Open(string connString)
                {
                    if (_internalConnection == null)
                        _internalConnection = new SqlConnection(connString);
                    if (_internalConnection.State != ConnectionState.Open)
                        _internalConnection.Open();
                }
                public void Close()
                {
                    if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                        _internalConnection.Close();
                }
        
                //SQL string
                private const string SQL_DOCTYPE_INSERT = "INSERT INTO DOCUMENTTYPE(NAME, DESCRIPTION, LANG, STANDARDID, INDUSTRYID) VALUES (@NAME, @DESCRIPTION, @LANG, @STANDARDID, @INDUSTRYID); SELECT ID=@@IDENTITY;";
                private const string SQL_DOCTYPE_DELETE = "DELETE FROM DOCUMENTTYPE WHERE ID=@ID";
        
                private const string SQL_DOCTYPE_UPDATE = "UPDATE DOCUMENTTYPE SET NAME=@NAME, DESCRIPTION=@DESCRIPTION WHERE ID=@ID AND STANDARDID=@STANDARDID AND INDUSTRYID=@INDUSTRYID AND LANG=@LANG";
        
                private const string SQL_DOCTYPE_GETALL = "SELECT * FROM V_DOCTYPE";
        
                private const string SQL_DOCTYPE_BYINDUSTRY_BYSTANDARD_BYLANG = "SELECT * FROM V_DOCTYPE WHERE INDUSTRY=@INDUSTRY AND STANDARD = @STANDARD AND LANG = @LANG";
        
                private const string SQL_DOCTYPE_CHECKEXISTED = "SELECT * FROM V_DOCTYPE WHERE NAME=@NAME AND LANG=@LANG AND INDUSTRYID=@INDUSTRYID";
        
                #region Parameters
                private const string PARM_ID = "@ID";
                private const string PARM_NAME = "@NAME";
                private const string PARM_DESCRIPTION = "@DESCRIPTION";
                private const string PARM_LANG = "@LANG";
                private const string PARM_STANDARDID = "@STANDARDID";
                private const string PARM_STANDARD = "@STANDARD";
                private const string PARM_INDUSTRYID = "@INDUSTRYID";
                private const string PARM_INDUSTRY = "@INDUSTRY";
                #endregion
        
                //Functions
        
                #region IDocType Members
        
        
                public int InsertDocType(DocsTypeData item)
                {
                    try
                    {
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_NAME, SqlDbType.NVarChar),
                            new SqlParameter(PARM_DESCRIPTION, SqlDbType.NText),
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_STANDARDID, SqlDbType.Int),
                            new SqlParameter(PARM_INDUSTRYID, SqlDbType.Int)};
                        parms[0].Value = item.Name;
                        parms[1].Value = item.Description;
                        parms[2].Value = item.Lang;
                        parms[3].Value = item.Standard;
                        parms[4].Value = item.Industry;
                        return SQLHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DOCTYPE_INSERT, parms);
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public void Update(DocsTypeUI item)
                {
                    try
                    {
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_NAME, SqlDbType.NVarChar),
                            new SqlParameter(PARM_DESCRIPTION, SqlDbType.NText),
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_STANDARDID, SqlDbType.Int),
                            new SqlParameter(PARM_INDUSTRYID, SqlDbType.Int),
                            new SqlParameter(PARM_ID, SqlDbType.Int)};
                        parms[0].Value = item.Name;
                        parms[1].Value = item.Description;
                        parms[2].Value = item.Lang;
                        parms[3].Value = item.Standard;
                        parms[4].Value = item.Industry;
                        parms[5].Value = item.Id;
                        SQLHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DOCTYPE_UPDATE, parms);
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public void Delete(int id)
                {
                    try
                    {
                        SqlParameter parm1 = new SqlParameter(PARM_ID, SqlDbType.Int);
                        parm1.Value = id;
                        SQLHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DOCTYPE_DELETE, parm1);
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public List<DocsTypeUI> GetDocType()
                {
                    try
                    {
                        List<DocsTypeUI> doctypeList = new List<DocsTypeUI>();
                        SqlDataReader rdr = SQLHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DOCTYPE_GETALL);
                        while (rdr.Read())
                        {
                            DocsTypeUI item = new DocsTypeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["STANDARD"]), rdr["STANDARDTEXT"].ToString(), Convert.ToInt32(rdr["INDUSTRY"]), rdr["INDUSTRYTEXT"].ToString());
                            doctypeList.Add(item);
                        }
                        rdr.Close();
                        return doctypeList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                public bool CheckExisted(DocsTypeData item)
                {
                    try
                    {
                        List<DocsTypeUI> doctypeList = new List<DocsTypeUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_NAME, SqlDbType.NVarChar),
                            new SqlParameter(PARM_LANG, SqlDbType.Int),
                            new SqlParameter(PARM_INDUSTRYID, SqlDbType.Int)};
                        parms[0].Value = item.Name;
                        parms[1].Value = item.Lang;
                        parms[2].Value = item.Industry;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DOCTYPE_CHECKEXISTED, parms);
                        if (rdr.HasRows)
                            return true;
                        else
                            return false;
                    }
                    catch
                    {
                        throw;
                    }
        
                }
        
                public List<DocsTypeUI> GetDocTypeByIndustryAndStandardAndLang(int industry, int standard, int lang)
                {
                    try
                    {
                        List<DocsTypeUI> doctypeList = new List<DocsTypeUI>();
                        SqlParameter[] parms = new SqlParameter[] {
                            new SqlParameter(PARM_INDUSTRY, SqlDbType.Int),
                            new SqlParameter(PARM_STANDARD, SqlDbType.Int),
                            new SqlParameter(PARM_LANG, SqlDbType.Int)};
                        parms[0].Value = industry;
                        parms[1].Value = standard;
                        parms[2].Value = lang;
        
                        SqlDataReader rdr = SQLHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, SQL_DOCTYPE_BYINDUSTRY_BYSTANDARD_BYLANG, parms);
                        while (rdr.Read())
                        {
                            DocsTypeUI item = new DocsTypeUI(Convert.ToInt32(rdr["ID"]), rdr["NAME"].ToString(), rdr["DESCRIPTION"].ToString(), Convert.ToInt32(rdr["LANG"]), rdr["LANGTEXT"].ToString(), Convert.ToInt32(rdr["STANDARD"]), rdr["STANDARDTEXT"].ToString(), Convert.ToInt32(rdr["INDUSTRY"]), rdr["INDUSTRYTEXT"].ToString());
                            doctypeList.Add(item);
                        }
                        rdr.Close();
                        return doctypeList;
                    }
                    catch
                    {
                        throw;
                    }
                }
        
                #endregion*/
        
    }
}
